package com.dragon.codefx.utils;

import com.dragon.codefx.dataBase.entity.DatabaseConfigEntity;
import lombok.SneakyThrows;
import org.apache.commons.lang3.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.*;

import static com.dragon.codefx.utils.JdbcUtil.DatabaseType.SQLITE;
import static com.dragon.codefx.utils.JdbcUtil.identifyDatabaseType;
import static com.dragon.codefx.utils.SchemaUtils.buildJdbcUrlWithDefaultParams;


/**
 * 字段工具类
 * @author lsc
 * @since 2025/6/20 16:14
 **/
public class ColumnUtils {

    @SneakyThrows
    public static List<Map<String, Object>> columnList(DatabaseConfigEntity entity, String schemaName, String tableName) {

        String username = entity.getUsername();
        String password = entity.getPassword();
        String jdbcUrl = buildJdbcUrlWithDefaultParams(entity.getUrl(), entity.getPort(), entity.getType(), schemaName);
        // 基础校验
        if (StringUtils.isEmpty(jdbcUrl)) {
            throw new IllegalArgumentException("数据库连接URL");
        }
        if (StringUtils.isEmpty(tableName)) {
            throw new IllegalArgumentException("表名不能为空");
        }
        // 识别数据库类型并构建查询
        JdbcUtil.DatabaseType dbType = identifyDatabaseType(jdbcUrl);
        String columnQuerySql = getColumnQuerySql(dbType, tableName);

        // 执行查询
        return executeColumnQuery(columnQuerySql, jdbcUrl, username, password, schemaName, tableName, dbType);
    }

    /**
     * 获取列信息查询SQL
     */
    private static String getColumnQuerySql(JdbcUtil.DatabaseType dbType, String tableName) {
        switch (dbType) {
            case MYSQL:
                return "SELECT COLUMN_NAME AS column_name, DATA_TYPE AS data_type, " +
                        "COLUMN_TYPE AS column_type, COLUMN_COMMENT AS column_comment, " +
                        "IS_NULLABLE AS is_nullable, COLUMN_KEY AS column_key, " +
                        "COLUMN_DEFAULT AS column_default, EXTRA AS extra " +
                        "FROM INFORMATION_SCHEMA.COLUMNS " +
                        "WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? " +
                        "ORDER BY ORDINAL_POSITION";
            case ORACLE:
                return "SELECT COLUMN_NAME AS column_name, DATA_TYPE AS data_type, " +
                        "DATA_LENGTH AS data_length, NULLABLE AS is_nullable, " +
                        "DATA_DEFAULT AS column_default, COMMENTS AS column_comment " +
                        "FROM ALL_TAB_COLUMNS c LEFT JOIN ALL_COL_COMMENTS cc ON " +
                        "c.TABLE_NAME = cc.TABLE_NAME AND c.COLUMN_NAME = cc.COLUMN_NAME " +
                        "AND c.OWNER = cc.OWNER " +
                        "WHERE c.OWNER = ? AND c.TABLE_NAME = ? " +
                        "ORDER BY COLUMN_ID";
            case SQLSERVER:
                return "SELECT c.name AS column_name, t.name AS data_type, " +
                        "c.max_length AS data_length, c.is_nullable, " +
                        "c.default_value AS column_default, " +
                        "ep.value AS column_comment " +
                        "FROM sys.columns c " +
                        "JOIN sys.tables t ON c.object_id = t.object_id " +
                        "JOIN sys.schemas s ON t.schema_id = s.schema_id " +
                        "LEFT JOIN sys.extended_properties ep ON " +
                        "ep.major_id = c.object_id AND ep.minor_id = c.column_id " +
                        "AND ep.name = 'MS_Description' " +
                        "WHERE s.name = ? AND t.name = ? " +
                        "ORDER BY c.column_id";
            case POSTGRESQL:
                return "SELECT column_name, data_type, " +
                        "is_nullable, column_default, " +
                        "pgd.description AS column_comment " +
                        "FROM information_schema.columns c " +
                        "LEFT JOIN pg_catalog.pg_statio_all_tables st ON " +
                        "c.table_schema = st.schemaname AND c.table_name = st.relname " +
                        "LEFT JOIN pg_catalog.pg_description pgd ON " +
                        "pgd.objoid = st.relid AND pgd.objsubid = c.ordinal_position " +
                        "WHERE table_schema = ? AND table_name = ? " +
                        "ORDER BY ordinal_position";
            case DB2:
                return "SELECT COLNAME AS column_name, TYPENAME AS data_type, " +
                        "LENGTH AS data_length, NULLS AS is_nullable, " +
                        "DEFAULT AS column_default, REMARKS AS column_comment " +
                        "FROM SYSCAT.COLUMNS " +
                        "WHERE TABSCHEMA = ? AND TABNAME = ? " +
                        "ORDER BY COLNO";
            case SQLITE:
                return String.format("PRAGMA table_info(%s)", tableName);
            default:
                throw new UnsupportedOperationException("不支持的数据库类型: " + dbType);
        }
    }

    /**
     * 执行列信息查询
     */
    @SneakyThrows
    private static List<Map<String, Object>> executeColumnQuery(String sql, String jdbcUrl,
                                                                String username, String password,
                                                                String schemaName, String tableName,
                                                                JdbcUtil.DatabaseType dbType) {
        try (Connection connection = ConnectionManager.getConnection(jdbcUrl, username, password);
             PreparedStatement statement = connection.prepareStatement(sql)) {

            // 设置参数
            if (dbType != SQLITE) {
                // SQLite特殊处理，只需要表名
                statement.setString(1, schemaName);
                statement.setString(2, tableName);
            }

            try (ResultSet resultSet = statement.executeQuery()) {
                List<Map<String, Object>> result = new ArrayList<>();
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();

                while (resultSet.next()) {
                    Map<String, Object> row = new LinkedHashMap<>();
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = metaData.getColumnLabel(i);
                        Object value = resultSet.getObject(i);
                        row.put(columnName, value);
                    }

                    // SQLite特殊处理，标准化返回字段
                    if (dbType == SQLITE) {
                        standardizeSqliteResult(row);
                    }

                    result.add(row);
                }

                return result;
            }
        }
    }

    /**
     * 标准化SQLite返回结果
     */
    private static void standardizeSqliteResult(Map<String, Object> row) {
        // SQLite的PRAGMA返回字段与其它数据库不同，这里做标准化处理
        Map<String, String> mapping = new HashMap<>();
        mapping.put("name", "column_name");
        mapping.put("type", "data_type");
        mapping.put("notnull", "is_nullable");
        mapping.put("dflt_value", "column_default");
        mapping.put("pk", "column_key");

        // 重命名字段并转换值
        for (Map.Entry<String, String> entry : mapping.entrySet()) {
            if (row.containsKey(entry.getKey())) {
                Object value = row.remove(entry.getKey());
                if ("is_nullable".equals(entry.getValue())) {
                    value = "1".equals(String.valueOf(value)) ? "NO" : "YES";
                }
                if ("pk".equals(entry.getKey())) {
                    value = "1".equals(String.valueOf(value)) ? "PRI" : null;
                }
                row.put(entry.getValue(), value);
            }
        }

        // 添加缺少的标准字段
        row.putIfAbsent("column_comment", "");
        row.putIfAbsent("column_key", "");
        row.putIfAbsent("extra", "");
    }
}
